package ar.com.android.alco.providers;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import ar.com.android.alco.dbHelpers.SQLALCOHelper;
import ar.com.android.alco.entities.RegistroComidas;

public class RegistroComidasProvider {

	private static final String DB_COLUMN_0_NAME = "idRegistroComidas";
	private static final String DB_COLUMN_1_NAME = "fechaHoraRegistro";
	private static final String DB_COLUMN_2_NAME = "idRegistro";
	private static final String DB_COLUMN_3_NAME = "descripcion";
	private static final String DB_COLUMN_4_NAME = "creditos";
	

	private SQLiteDatabase database;
	private SQLALCOHelper dbHelper;
	private String[] allColumns = {DB_COLUMN_0_NAME, DB_COLUMN_1_NAME, DB_COLUMN_2_NAME, DB_COLUMN_3_NAME, DB_COLUMN_4_NAME};
	
	public RegistroComidasProvider(Context context) {
		dbHelper = SQLALCOHelper.getHelper(context);
		database = dbHelper.getWritableDatabase();
	}
		
	public long insertRegistroComidas(RegistroComidas registroComidas)
	{
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		
		ContentValues contentValues = new ContentValues();
		contentValues.put(DB_COLUMN_0_NAME, registroComidas.getIdRegistroComidas());
		contentValues.put(DB_COLUMN_1_NAME, sdf.format(registroComidas.getFechaHoraRegistro()));
		contentValues.put(DB_COLUMN_2_NAME, registroComidas.getIdRegistro());
		contentValues.put(DB_COLUMN_3_NAME, registroComidas.getDescripcion());
		contentValues.put(DB_COLUMN_4_NAME, registroComidas.getCreditos());
		
		long insertId = database.insert(SQLALCOHelper.DB_REGISTRO_COMIDAS_TABLE_NAME, null, contentValues);
		return insertId;
	}
	
	public boolean removeRegistroComidas(Integer idRegistroComidas)
	{
		int result = database.delete(SQLALCOHelper.DB_REGISTRO_COMIDAS_TABLE_NAME, DB_COLUMN_0_NAME + " = " + idRegistroComidas, null);
				
		if(result > 0)            
			return true;        
		else            
			return false;
	}
	
	public boolean removeRegistroComidas()
	{
		int result = database.delete(SQLALCOHelper.DB_REGISTRO_COMIDAS_TABLE_NAME, null, null);
				
		if(result > 0)            
			return true;        
		else            
			return false;
	}
		
	public long updateRegistroComidas(RegistroComidas registroComidas)
	{
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		
		ContentValues contentValues = new ContentValues();
		contentValues.put(DB_COLUMN_1_NAME, sdf.format(registroComidas.getFechaHoraRegistro()));
		contentValues.put(DB_COLUMN_2_NAME, registroComidas.getIdRegistro());
		contentValues.put(DB_COLUMN_3_NAME, registroComidas.getDescripcion());
		contentValues.put(DB_COLUMN_4_NAME, registroComidas.getCreditos());
		
		return database.update(SQLALCOHelper.DB_REGISTRO_COMIDAS_TABLE_NAME, contentValues, DB_COLUMN_0_NAME + " = " + registroComidas.getIdRegistroComidas(), null);
	}
	
	public RegistroComidas getRegistroComidas(Integer idRegistroComidas)
	{
		try
		{
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			
			Cursor cursor = database.query(SQLALCOHelper.DB_REGISTRO_COMIDAS_TABLE_NAME, allColumns, DB_COLUMN_0_NAME + " = " + idRegistroComidas, null, null, null, null);
			cursor.moveToFirst();
			
			RegistroComidas registroComidas = new RegistroComidas();
			registroComidas.setIdRegistroComidas(cursor.getInt(0));
			registroComidas.setFechaHoraRegistro(sdf.parse(cursor.getString(1)));
			registroComidas.setIdRegistro(cursor.getInt(2));
			registroComidas.setDescripcion(cursor.getString(3));
			registroComidas.setCreditos(cursor.getDouble(4));

			cursor.close();
			return registroComidas;
	
		}
		catch (Exception e) {
			return null;
		}		
	}
	
	public Double getSumaCreditosDia(String fechaHora) {
		try
		{
			SimpleDateFormat sdfDB = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			SimpleDateFormat sdfTime = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
			
			Date dateFrom = sdfTime.parse(fechaHora + " 00:00:00");
			Date dateTo = sdfTime.parse(fechaHora + " 24:00:00");
			
			
			String strSQL = "SELECT SUM(" + DB_COLUMN_4_NAME + ") AS canCreditos FROM " + SQLALCOHelper.DB_REGISTRO_COMIDAS_TABLE_NAME +
							" WHERE fechaHoraRegistro BETWEEN '" + sdfDB.format(dateFrom) + "' AND '" + sdfDB.format(dateTo) + "'";
			Cursor cursor = database.rawQuery(strSQL, null);
			cursor.moveToFirst();

			if(cursor.getString(0) == null)
				return 0D; 
			return cursor.getDouble(0);
		}
		catch (Exception e) {
			return 0D;
		}
	}
	
	public RegistroComidas getRegistroComidas(String fechaHora, Integer idRegistro) {
		try
		{
			SimpleDateFormat sdfDB = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			SimpleDateFormat sdfTime = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
			
			Date dateFrom = sdfTime.parse(fechaHora + " 00:00:00");
			Date dateTo = sdfTime.parse(fechaHora + " 24:00:00");
			
			
			String strSQL = "SELECT * FROM " + SQLALCOHelper.DB_REGISTRO_COMIDAS_TABLE_NAME +
							" WHERE fechaHoraRegistro BETWEEN '" + sdfDB.format(dateFrom) + "' AND '" + sdfDB.format(dateTo) + "' AND idRegistro = " + idRegistro;
			Cursor cursor = database.rawQuery(strSQL, null);
			cursor.moveToFirst();

			RegistroComidas registroComidas = new RegistroComidas();
			registroComidas.setIdRegistroComidas(cursor.getInt(0));
			registroComidas.setFechaHoraRegistro(sdfDB.parse(cursor.getString(1)));
			registroComidas.setIdRegistro(cursor.getInt(2));
			registroComidas.setDescripcion(cursor.getString(3));
			registroComidas.setCreditos(cursor.getDouble(4));

			cursor.close();
			return registroComidas;

		}
		catch (Exception e) {
			return null;
		}
	}
	
	public List<RegistroComidas> getRegistrosComidas() {
		try
		{
			List<RegistroComidas> lstRegistroComidas = new ArrayList<RegistroComidas>();
			
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			
			Cursor cursor = database.query(SQLALCOHelper.DB_REGISTRO_COMIDAS_TABLE_NAME, allColumns, null, null, null, null, null);
			cursor.moveToFirst();
			
			while (!cursor.isAfterLast()) {
				RegistroComidas registroComidas = new RegistroComidas();
				registroComidas.setIdRegistroComidas(cursor.getInt(0));
				registroComidas.setFechaHoraRegistro(sdf.parse(cursor.getString(1)));
				registroComidas.setIdRegistro(cursor.getInt(2));
				registroComidas.setDescripcion(cursor.getString(3));
				registroComidas.setCreditos(cursor.getDouble(4));
				
				lstRegistroComidas.add(registroComidas);
				cursor.moveToNext();
			}
			cursor.close();
			return lstRegistroComidas;
		}
		catch (Exception e) {
			return new ArrayList<RegistroComidas>();
		}
	}
	
	public List<RegistroComidas> getRegistrosComidas(String fechaHora) {
		try
		{
			List<RegistroComidas> lstRegistroComidas = new ArrayList<RegistroComidas>();
						
			SimpleDateFormat sdfDB = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			SimpleDateFormat sdfTime = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
			
			Date dateFrom = sdfTime.parse(fechaHora + " 00:00:00");
			Date dateTo = sdfTime.parse(fechaHora + " 24:00:00");
			
			String strWhere = "fechaHoraRegistro BETWEEN '" + sdfDB.format(dateFrom) + "' AND '" + sdfDB.format(dateTo) + "'";
			
			Cursor cursor = database.query(SQLALCOHelper.DB_REGISTRO_COMIDAS_TABLE_NAME, allColumns, strWhere, null, null, null, DB_COLUMN_2_NAME);
			cursor.moveToFirst();
			
			while (!cursor.isAfterLast()) {
				RegistroComidas registroComidas = new RegistroComidas();
				registroComidas.setIdRegistroComidas(cursor.getInt(0));
				registroComidas.setFechaHoraRegistro(sdfDB.parse(cursor.getString(1)));
				registroComidas.setIdRegistro(cursor.getInt(2));
				registroComidas.setDescripcion(cursor.getString(3));
				registroComidas.setCreditos(cursor.getDouble(4));
				
				lstRegistroComidas.add(registroComidas);
				cursor.moveToNext();
			}
			cursor.close();
			return lstRegistroComidas;
		}
		catch (Exception e) {
			return new ArrayList<RegistroComidas>();
		}
	}
}
